package com.maxd.respository.helpTask;

import com.maxd.model.HelpSendOrder;
import com.maxd.model.HelpTaskDetails;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

public interface HelpSendOrderRepository extends Repository<HelpSendOrder, Long> {

    @Query(value = "select * from help_send_order s where s.help_task_id=:helpTaskId and s.user_id=:userId",nativeQuery = true)
    HelpSendOrder selectByHelpTaskIdAndUserId(@Param("helpTaskId") Long helpTaskId,@Param("userId") Long userId);

    @Query(value = "select s.id ,t.title,t.task_original_price as taskOriginalPrice,u.id as userId, " +
            "u.nick_name as nickName,s.create_time as createTime,s.state,s.content,t.task_price as money, " +
            "s.audit_content as auditContent,s.category,s.audit_time as auditTime " +
            "from help_send_order s " +
            "left join help_task t on s.help_task_id=t.id " +
            "left join user_info u on s.user_id=u.id " +
            "where 1=1 and if(:phone!='',u.phone=:phone,1=1) and if(:state!=-1 , s.state=:state,1=1) order by s.create_time desc ",
            countQuery = "select count(*) " +
                    "from help_send_order s " +
                    "left join help_task t on s.help_task_id=t.id " +
                    "left join user_info u on s.user_id=u.id " +
                    "where 1=1 and if(:phone!='',u.phone=:phone,1=1) and if(:state!=-1 , s.state=:state,1=1) order by s.create_time desc "
            ,nativeQuery = true)
    Page<Map<String,Object>> selectSendOrderList(Pageable pageable, String phone, Integer state);

    @Query(value = "select s.id ,u.id as userId, " +
            "u.nick_name as nickName,s.create_time as createTime,s.state,s.content,t.task_price as money, " +
            "s.audit_content as auditContent,s.category,s.audit_time as auditTime " +
            "from help_send_order s " +
            "left join help_task t on t.id=s.help_task_id " +
            "left join user_info u on s.user_id=u.id " +
            "where help_task_id=:helpTaskId order by s.create_time desc ",
            countQuery = "select count(*) " +
                    "from help_send_order " +
                    "left join user_info u on s.user_id=u.id " +
                    "where help_task_id=:helpTaskId order by s.create_time desc "
            ,nativeQuery = true)
    Page<Map<String,Object>> selectSendOrderByTaskId(Pageable pageable,Long helpTaskId);


    @Query(value = "select sum(picture) from help_send_order where state=2 and date_format(create_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')" ,nativeQuery = true)
    Double sumMoneyBySend1(String time);

    @Query(value = "select sum(picture) from help_send_order where state=2 and date_format(create_time,'%Y-%m')=date_format(:time,'%Y-%m')" ,nativeQuery = true)
    Double sumMoneyBySend2(String time);

    @Query(value = "select sum(picture) from help_send_order where state=2 and date_format(create_time,'%Y')=date_format(:time,'%Y')" ,nativeQuery = true)
    Double sumMoneyBySend3(String time);

    @Query(value = "select count(*) from help_send_order where state=2 and date_format(create_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')" ,nativeQuery = true)
    int countBySend1(String time);

    @Query(value = "select count(*) from help_send_order where state=2 and date_format(create_time,'%Y-%m')=date_format(:time,'%Y-%m')" ,nativeQuery = true)
    int countBySend2(String time);

    @Query(value = "select count(*) from help_send_order where state=2 and date_format(create_time,'%Y')=date_format(:time,'%Y')" ,nativeQuery = true)
    int countBySend3(String time);


    @Query(value = "select 1 from help_send_order s where s.help_task_id=:helpTaskId and s.user_id=:userId limit 1",nativeQuery = true)
    Integer selectCountByHelpTaskIdAndUserId(@Param("helpTaskId") Long helpTaskId,@Param("userId") Long userId);

    @Query(value = "select * from help_send_order s where s.id=:helpSendOrderId ",nativeQuery = true)
    HelpSendOrder selectById(@Param("helpSendOrderId") Long helpSendOrderId);

    @Transactional
    @Modifying
    @Query(value = "update help_send_order set state=:state,audit_content=:auditContent,category=:category,audit_time=:auditTime where id=:id",nativeQuery = true)
    Integer updateHelpSendOrder(@Param("id") Long id,@Param("state") Integer state,@Param("auditContent") String auditContent,@Param("category") Integer category,@Param("auditTime") String auditTime);

    @Transactional
    @Modifying
    @Query(value = "update help_send_order set state=:state,content=:content where id=:id",nativeQuery = true)
    Integer updateHelpSendOrder(@Param("id") Long id,@Param("state") Integer state,@Param("content") String content);

    @Transactional
    @Modifying
    @Query(value = "update help_send_order set state=:state where id=:id",nativeQuery = true)
    Integer update(@Param("id") Long id,@Param("state") Integer state);

    @Query(value = "select count(*) from help_send_order s where s.help_task_id=:helpTaskId and s.state=:state ",nativeQuery = true)
    Integer selectResidueNum(@Param("helpTaskId") Long helpTaskId,@Param("state") Integer state);

    @Query(value = "select count(*) from help_send_order s where s.help_task_id=:helpTaskId and s.state in (3,5,6) ",nativeQuery = true)
    Integer selectEndNum(@Param("helpTaskId") Long helpTaskId);


}
